package holo.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import util.YtJSONObject;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author Malegod_xiaofei
 * @create 2022-06-10-11:55
 */
public class Update_Holo_2022_0618 {
    //定义SQL 链接 预编译器
    private static DataSource dataSource = null;
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;

    public static void main(String[] args) throws Exception {
        String[] month = {"20220618"};
        Map<String, JSONObject> check_region_id = new HashMap<>();
//        check_region_id.put("130629", new YtJSONObject().put("new_region_id", "139003").put("province", "河北省").put("city", "省直辖县级行政区划").put("district", "雄安新区").getHome());
//        check_region_id.put("130632", new YtJSONObject().put("new_region_id", "139003").put("province", "河北省").put("city", "省直辖县级行政区划").put("district", "雄安新区").getHome());
//        check_region_id.put("130638", new YtJSONObject().put("new_region_id", "139003").put("province", "河北省").put("city", "省直辖县级行政区划").put("district", "雄安新区").getHome());
//        check_region_id.put("232723", new YtJSONObject().put("new_region_id", "232701").put("province", "黑龙江省").put("city", "大兴安岭地区").put("district", "漠河市").getHome());
//        check_region_id.put("340221", new YtJSONObject().put("new_region_id", "340210").put("province", "安徽省").put("city", "芜湖市").put("district", "湾沚区").getHome());
//        check_region_id.put("320602", new YtJSONObject().put("new_region_id", "320613").put("province", "江苏省").put("city", "南通市").put("district", "崇川区").getHome());
//        check_region_id.put("350403", new YtJSONObject().put("new_region_id", "350404").put("province", "福建省").put("city", "三明市").put("district", "三元区").getHome());
//        check_region_id.put("370684", new YtJSONObject().put("new_region_id", "370614").put("province", "山东省").put("city", "烟台市").put("district", "蓬莱区").getHome());
//        check_region_id.put("130521", new YtJSONObject().put("new_region_id", "130502").put("province", "河北省").put("city", "邢台市").put("district", "襄都区").getHome());
//        check_region_id.put("140202", new YtJSONObject().put("new_region_id", "140213").put("province", "山西省").put("city", "大同市").put("district", "平城区").getHome());
//        check_region_id.put("320504", new YtJSONObject().put("new_region_id", "320508").put("province", "江苏省").put("city", "苏州市").put("district", "姑苏区").getHome());
//        check_region_id.put("320829", new YtJSONObject().put("new_region_id", "320813").put("province", "江苏省").put("city", "淮安市").put("district", "洪泽区").getHome());
//        check_region_id.put("330283", new YtJSONObject().put("new_region_id", "330213").put("province", "浙江省").put("city", "宁波市").put("district", "奉化市").getHome());
//        check_region_id.put("331021", new YtJSONObject().put("new_region_id", "331083").put("province", "浙江省").put("city", "台州市").put("district", "玉环市").getHome());
//        check_region_id.put("340222", new YtJSONObject().put("new_region_id", "340211").put("province", "安徽省").put("city", "芜湖市").put("district", "繁昌区").getHome());
//        check_region_id.put("340225", new YtJSONObject().put("new_region_id", "340281").put("province", "安徽省").put("city", "芜湖市").put("district", "无为市").getHome());
//        check_region_id.put("341822", new YtJSONObject().put("new_region_id", "341882").put("province", "安徽省").put("city", "宣城市").put("district", "广德市").getHome());
//        check_region_id.put("370181", new YtJSONObject().put("new_region_id", "370114").put("province", "山东省").put("city", "济南市").put("district", "章丘市").getHome());
//        check_region_id.put("370282", new YtJSONObject().put("new_region_id", "370215").put("province", "山东省").put("city", "青岛市").put("district", "即墨区").getHome());
//        check_region_id.put("430124", new YtJSONObject().put("new_region_id", "430182").put("province", "湖南省").put("city", "长沙市").put("district", "宁乡市").getHome());
//        check_region_id.put("510626", new YtJSONObject().put("new_region_id", "510604").put("province", "四川省").put("city", "德阳市").put("district", "罗江区").getHome());
//        check_region_id.put("510922", new YtJSONObject().put("new_region_id", "510981").put("province", "四川省").put("city", "遂宁市").put("district", "射洪市").getHome());
//        check_region_id.put("520321", new YtJSONObject().put("new_region_id", "520304").put("province", "贵州省").put("city", "遵义市").put("district", "播州区").getHome());
//        check_region_id.put("632321", new YtJSONObject().put("new_region_id", "632301").put("province", "青海省").put("city", "黄南藏族自治州").put("district", "同仁市").getHome());
//        check_region_id.put("360105", new YtJSONObject().put("new_region_id", "360112").put("province", "江西省").put("city", "南昌市").put("district", "新建区").getHome());

        check_region_id.put("130682", new YtJSONObject().put("new_region_id", "139003").put("province", "河北省").put("city", "省直辖县级行政区划").put("district", "定州市").getHome());
        check_region_id.put("140211", new YtJSONObject().put("new_region_id", "140214").put("province", "山西省").put("city", "大同市").put("district", "云冈区").getHome());
        check_region_id.put("350625", new YtJSONObject().put("new_region_id", "350605").put("province", "福建省").put("city", "漳州市").put("district", "长泰区").getHome());
        check_region_id.put("370634", new YtJSONObject().put("new_region_id", "370614").put("province", "山东省").put("city", "烟台市").put("district", "蓬莱区").getHome());
        check_region_id.put("371200", new YtJSONObject().put("new_region_id", "371202").put("province", "山东省").put("city", "济南市").put("district", "莱芜区").getHome());
        check_region_id.put("371202", new YtJSONObject().put("new_region_id", "370116").put("province", "山东省").put("city", "济南市").put("district", "莱芜区").getHome());
        check_region_id.put("371203", new YtJSONObject().put("new_region_id", "370117").put("province", "山东省").put("city", "济南市").put("district", "钢城区").getHome());
        check_region_id.put("410381", new YtJSONObject().put("new_region_id", "410307").put("province", "河南省").put("city", "洛阳市").put("district", "偃师区").getHome());
        check_region_id.put("410322", new YtJSONObject().put("new_region_id", "410308").put("province", "河南省").put("city", "洛阳市").put("district", "孟津区").getHome());
        check_region_id.put("421023", new YtJSONObject().put("new_region_id", "421088").put("province", "湖北省").put("city", "荆州市").put("district", "监利市").getHome());
        check_region_id.put("441902", new YtJSONObject().put("new_region_id", "441931").put("province", "广东省").put("city", "东莞市").put("district", "南城街道").getHome());
        check_region_id.put("441904", new YtJSONObject().put("new_region_id", "441935").put("province", "广东省").put("city", "东莞市").put("district", "东城街道").getHome());
        check_region_id.put("441906", new YtJSONObject().put("new_region_id", "441917").put("province", "广东省").put("city", "东莞市").put("district", "万江街道").getHome());
        check_region_id.put("441911", new YtJSONObject().put("new_region_id", "441936").put("province", "广东省").put("city", "东莞市").put("district", "莞城街道").getHome());
        check_region_id.put("450127", new YtJSONObject().put("new_region_id", "450181").put("province", "广西壮族自治区").put("city", "南宁市").put("district", "横州市").getHome());
        check_region_id.put("500200", new YtJSONObject().put("new_region_id", "500100").put("province", "重庆市").put("city", "重庆市").put("district", "").getHome());
        check_region_id.put("530522", new YtJSONObject().put("new_region_id", "530581").put("province", "云南省").put("city", "保山市").put("district", "腾冲市").getHome());
        check_region_id.put("542400", new YtJSONObject().put("new_region_id", "540600").put("province", "西藏自治区").put("city", "那曲市").put("district", "").getHome());
        check_region_id.put("652200", new YtJSONObject().put("new_region_id", "650500").put("province", "新疆维吾尔自治区").put("city", "哈密市").put("district", "").getHome());

        String sql = "";
        for (int i = 0; i < month.length; i++) {
            for (Map.Entry<String, JSONObject> s : check_region_id.entrySet()) {
                System.out.println("月份 : " + month[i]);
//                sql = "update dbadmin.o2o_region_category_app_tui_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_showlive_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_tourism_idl_tui_" + month[i] + " set region_id_out = '" + s.getValue().getString("new_region_id") + "',province_out = '" + s.getValue().getString("province") + "',city_out = '" + s.getValue().getString("city") + "',district_out = '" + s.getValue().getString("district") + "' where region_id_out = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_allplat_shop_bdl_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
                sql = "update dbadmin.o2o_anchor_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
                System.out.println("更新 sql :" + sql);
                update(sql);
            }
        }

    }

    public static void select(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 打印 resultSet 数据
        ResultSetMetaData resultSetmd = resultSet.getMetaData();
        int columnsNumber = resultSetmd.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + " " + resultSetmd.getColumnName(i));
            }
            System.out.println("");
        }
        preparedStatement.close();
    }

    public static void update(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();
        // 打印 resultSet 数据
        System.out.println("一共 : " + resultSet + " 条数据受影响 ! ");
        preparedStatement.close();

    }


}